import os
import sqlite3

dbFileName = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'stock_sqlite3.db')



con = None

def builtinTypeToDbType(bType):
    result = {
    int: 'INTEGER',
    str: 'TEXT',
    float: 'REAL',
    }.get(bType, 'NULL')
    return result

def open():
    global con
    if not con:
        con = sqlite3.connect(dbFileName)
    return con

def close():
    global con
    if con:
        con.close()

def getAllTable():
    result = []
    try:
        sqlString = "SELECT name FROM sqlite_master WHERE type='table'"

        cur = con.cursor()
        cur.execute(sqlString)
        result = cur.fetchall()
    except Exception, e:
        print e
    return result

def getTable(table):
    result = []
    try:
        sqlString = "SELECT name FROM sqlite_master WHERE type='table' AND tbl_name = (?)"

        cur = con.cursor()
        cur.execute(sqlString, (table,))
        result = cur.fetchall()
        return result
    except Exception, e:
        print e
    return result

def entryToKeyEntry(entry):
    keyEntry = {}
    for itemName in entry:
        if entry[itemName]['required']:
            keyEntry[itemName] = entry[itemName]['value']
    return keyEntry

def isTableExist(table):
    '''
        result = []
        try:
            sqlString = "SELECT name FROM sqlite_master WHERE type='table' AND name='%s'" % table

            cur = con.cursor()
            cur.execute(sqlString)
            result = cur.fetchall()
        except Exception, e:
            print e
        return (False if result == [] else True)
    '''
    tables = getAllTable()
    for tableName in tables:
        if table == tableName[0]:
            return True
    return False

# entry = {name :{'value', 'type', 'required'}}
def createTable(table, entry):
    try:
        columnStr = ''
        for itemName in entry:
            typeStr = builtinTypeToDbType(entry[itemName]['type'])
            if 'NULL' == typeStr:
                return
            columnStr += '{name} {type}'.format(name=itemName, type=typeStr)
            if entry[itemName]['required']:
                columnStr += ' PRIMARY KEY'
            columnStr += ','
        columnStr = columnStr[:-1]

        sqlString = "CREATE TABLE %s (%s)" % (table, columnStr)

        cur = con.cursor()
        cur.execute(sqlString)
        con.commit()
    except Exception, e:
        print e
    return

def DropTable(table):
    con = open()
    if (not con) or (not table):
        return

    try:
        sqlString = "DROP TABLE IF EXISTS %s" % table

        cur = con.cursor()
        cur.execute(sqlString)
        con.commit()
    except Exception, e:
        print e
    return

# entry = {name :{'value', 'type', 'required'}}
def AddColumn(table, entry):
    try:
        description = GetEntry(table, {})[0]
        columnStr = ''
        for itemName in entry:
            if itemName in description:
                continue
            typeStr = builtinTypeToDbType(entry[itemName]['type'])
            if 'NULL' == typeStr:
                return
            columnStr += '{name} {type}'.format(name=itemName, type=typeStr)
            if entry[itemName]['required']:
                columnStr += ' PRIMARY KEY'
            columnStr += ','
        columnStr = columnStr[:-1]

        sqlString = "ALTER TABLE %s ADD COLUMN %s" % (table, columnStr)
        #print sqlString

        cur = con.cursor()
        cur.execute(sqlString)
        con.commit()
    except Exception, e:
        print e
    return

class AddStatus:
    done = 0
    duplicate = 1
    newColumn = 2
    error = 3

# entry = {name :{'value', 'type', 'required'}}
def AddEntryToTable(table, entry):
    try:
        columnNames = ''
        qMarks = ''
        parameters = []
        for itemName in entry:
            if None == entry[itemName]['value']:
                continue
            columnNames += itemName + ', '
            qMarks += '?, '
            parameters.append(entry[itemName]['value'])
        columnNames = columnNames[:-2]
        qMarks = qMarks[:-2]

        sqlString = "INSERT INTO %s (%s) VALUES (%s)" % (table, columnNames, qMarks)
        #print sqlString, parameters

        cur = con.cursor()
        cur.execute(sqlString, parameters)
        con.commit()
    except Exception, e:
        if str(e) == 'column name is not unique':
            return AddStatus.done
        elif str(e).find('table Employee has no column named') >= 0:
            return AddStatus.newColumn
        else:
            print e
            return AddStatus.error
    return AddStatus.done

# entry = {name :{'value', 'type', 'required'}}
def AddEntry(table, entry):
    con = open()
    if (not con) or (not table):
        return

    if not isTableExist(table):
        createTable(table, entry)

    op = AddEntryToTable(table, entry)
    if op == AddStatus.done:
        return
    elif op == AddStatus.duplicate:
        ModifyEntry(table, entry)
    elif op == AddStatus.newColumn:
        AddColumn(table, entry)
        ModifyEntry(table, entry)
    return

# keyEntry = {name :value}
def DeleteEntry(table, keyEntry):
    con = open()
    if (not con) or (not table):
        return

    try:
        columnNames = ' WHERE '
        for itemName in keyEntry:
            columnNames += itemName + '=:' + itemName + ' AND '
        if 'AND' == columnNames[-4:-1]:
            columnNames = columnNames[:-5]
        else:
            columnNames = ''

        sqlString = "DELETE FROM %s" % table + columnNames

        cur = con.cursor()
        cur.execute(sqlString, keyEntry)
        con.commit()
    except Exception, e:
        print e
    return

def ModifyEntry(table, entry):
    keyEntry = entryToKeyEntry(entry)
    DeleteEntry(table, keyEntry)
    AddEntryToTable(table, entry)
    return

# keyEntry = {name :value}
def GetEntry(table, keyEntry):
    con = open()
    if (not con) or (not table):
        return

    try:
        columnNames = ' WHERE '
        for itemName in keyEntry:
            columnNames += itemName + '=:' + itemName + ' AND '
        if 'AND' == columnNames[-4:-1]:
            columnNames = columnNames[:-5]
        else:
            columnNames = ''

        sqlString = "SELECT * FROM %s" % table + columnNames

        cur = con.cursor()
        cur.execute(sqlString, keyEntry)
        result = cur.fetchall()
        #if [] != result:
        result.insert(0, [x[0] for x in cur.description])
        return result
    except Exception, e:
        print e
        return []
    return



    
